package dao;

import entity.Dept;
import entity.Meeting;
import entity.User;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import utils.DataSourceUtils;

import java.util.List;

public class MeetingDao {
    private JdbcTemplate jdbcTemplate = new JdbcTemplate(DataSourceUtils.getDataSource());

    /**
     * 没有会议状态查询会议
     * @param title
     * @param startPage
     * @param endPage
     * @return
     */
    public List<Meeting> findPage(String title, Integer startPage, Integer endPage) {
        try {
            String sql = "SELECT * FROM meeting m\n" +
                    "WHERE m.title like ? OR m.content like ? order by publish_date desc\n" +
                    "LIMIT ?,? ";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Meeting.class),
                    '%'+title+'%','%'+title+'%',startPage,endPage);
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }
    }

    /**
     * 有会议状态查询会议
     * @param title
     * @param statusL
     * @param startPage
     * @param endPage
     * @return
     */
    public List<Meeting> findPageS(String title, Long statusL, Integer startPage, Integer endPage) {
        try {
            String sql = "SELECT * FROM meeting m\n" +
                    "WHERE (m.title like ? OR m.content like ?)AND m.`status` = ? \n" +
                    "order by publish_date desc\n" +
                    "LIMIT ?,?";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Meeting.class),
                    '%'+title+'%','%'+title+'%',statusL,startPage,endPage);
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }
    }

    /**
     * 查询总的记录数
     * @param title
     * @return
     */
    public Integer findCount(String title) {
        try {
            String sql = "SELECT count(*) count FROM meeting m\n" +
                    "WHERE m.title like ? OR m.content like ? \n" ;
            return jdbcTemplate.queryForObject(sql, Integer.class, "%" + title + "%", "%" + title + "%");
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }
    }

    public Integer findCountS(String title, Long statusL) {
        try {
            String sql = "SELECT count(*) count FROM meeting m\n" +
                    "WHERE (m.title like ? OR m.content like ?)\n" +
                    "AND  m.`status` = ? \n";
            return jdbcTemplate.queryForObject(sql, Integer.class, "%" + title + "%", "%" + title + "%",statusL);
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }
    }

    /**
     * 根据部门id查部门名称
     * @param deptId
     * @return
     */
    public String findBydeptName(Long deptId) {
        try {
            String sql = "SELECT name FROM dept\n" +
                    "WHERE id = ?";
            return jdbcTemplate.queryForObject(sql, String.class, deptId);
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }
    }

    /**
     * 添加会议
     * @param meeting
     */
    public void insertMeeting(Meeting meeting) {
        try {
            jdbcTemplate.update("insert into meeting values(null,?,?,?,?,?,?,?,?,?)",
                    meeting.getDeptName(),
                    meeting.getDeptId(),
                    meeting.getTitle(),
                    meeting.getContent(),
                    meeting.getPublishDate(),
                    meeting.getStartTime(),
                    meeting.getEndTime(),
                    meeting.getStatus(),
                    meeting.getMakeUser()
            );
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
        }

    }

    /**
     * 查询所有部门
     */
    public List<Dept> findBydept() {
        try {
            String sql = "SELECT * FROM dept";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Dept.class));
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }
    }

    /**
     *  根据部门查询下面对应的员工
     * @param deptId
     * @return
     */
    public List<User> findByUser(Integer deptId) {
        try {
            String sql = "SELECT * FROM `user`\n" +
                    "WHERE dept_id = ?";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class), deptId);
        } catch (DataAccessException e) {
            //只是证明没有在数据库中找到这个用户，没有必要抛异常
            return null;
        }
    }

    /**
     * 查询会议详情
     * @param meetingId
     * @return
     */
    public Meeting findfindById(long meetingId) {
        try {
            return jdbcTemplate.queryForObject(
                    "select * from meeting where id=?",
                    new BeanPropertyRowMapper<>(Meeting.class), meetingId);
        } catch (EmptyResultDataAccessException exception) {
            // 如果是没有查询到则返回null
            return null;
        }

    }

    /**
     * 查询会议的实到人数
     * @param meetingId
     * @return
     */
    public Integer countJoinMeetingByMeetingId(Long meetingId) {
        try {
            return jdbcTemplate.queryForObject(
                    "select count(*) from meeting_join where m_id=?",
                    Integer.class, meetingId);
        } catch (EmptyResultDataAccessException exception) {
            // 如果是没有查询到则返回null
            return null;
        }
    }

    /**
     * 查询指定用户是否已经参加这次会议
     * @param userId
     * @param meetingId
     * @return
     */
    public Integer isJoinMeeting(Long userId, Long meetingId) {
        try {
            return jdbcTemplate.queryForObject(
                    "select count(*) from meeting_join where u_id=? and m_id=?",
                    Integer.class, userId, meetingId);
        } catch (EmptyResultDataAccessException exception) {
            // 如果是没有查询到则返回null
            return null;
        }
    }
    /**
     * 参加会议
     *
     * @param userId
     * @param meetingId
     */
    public void joinMeeting(Long userId, long meetingId) {

        jdbcTemplate.update("insert into meeting_join values(?,?)", userId, meetingId);

    }

    /**
     * 取消会议
     *
     * @param userId
     * @param meetingId
     */
    public void unJoinMeeting(Long userId, long meetingId) {
        jdbcTemplate.update("delete from meeting_join where u_id=? and m_id=? ", userId, meetingId);
    }

    /**
     * 查询用户是否参加了这次会议
     * @param userId
     * @param meetingId
     * @return
     */
    public Integer countByUserIdAndMeetingId(Long userId, Long meetingId) {
        return jdbcTemplate.queryForObject("select count(*) from meeting_join where m_id=? and u_id = ?",Integer.class,meetingId,userId);
    }
}
